package sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import beans.TestItemBean;
import beans.TestsViewBean;

public class TestCasesDao {
	private DataAccess dataAccess = new DataAccess();
	
	public TestsViewBean getTests(int userStoryId) throws ClassNotFoundException, SQLException {
		TestsViewBean testsViewBean = new TestsViewBean();

		String query = "SELECT TC.ID, TC.NAME, TC.DESCRIPTION, TC.EXPECTED_RESULTS"
				+ " FROM TEST_CASES TC WHERE USER_STORY_ID = " + userStoryId 
				+ " ORDER BY TC.ID";
		
		ResultSet rset = dataAccess.getSelectResult(query);
		
		while (rset.next()) {
			TestItemBean testItemBean = new TestItemBean();
			testItemBean.setId(rset.getInt("TC.ID"));
			testItemBean.setUserStoryId(userStoryId);
			testItemBean.setName(rset.getString("TC.NAME"));
			testItemBean.setDescription(rset.getString("TC.DESCRIPTION"));
			testItemBean.setExpectedResults(rset.getString("TC.EXPECTED_RESULTS"));

			testsViewBean.addRow(testItemBean);
		}
		dataAccess.closeConnection();

		return testsViewBean;
	}
	
	public int insert(TestItemBean testItemBean) throws ClassNotFoundException, SQLException {
		String query = "INSERT INTO TEST_CASES (NAME, DESCRIPTION, EXPECTED_RESULTS, USER_STORY_ID) VALUES (?, ?, ?, ?)";

		Connection conn = dataAccess.getConnection();
		PreparedStatement preparedStatement = conn.prepareStatement(query);

		preparedStatement.setString(1, testItemBean.getName());
		preparedStatement.setString(2, testItemBean.getDescription());
		preparedStatement.setString(3, testItemBean.getExpectedResults());
		preparedStatement.setInt(4, testItemBean.getUserStoryId());
		
		int rowsUpdated = dataAccess.executeUpdate(preparedStatement);
		if (rowsUpdated == 1) {
			// get id of new data
			ResultSet rset = dataAccess.getSelectResult("SELECT LAST_INSERT_ID()");
			while (rset.next()) {
				testItemBean.setId(rset.getInt(1));
			}
		}
		dataAccess.closeConnection();

		return rowsUpdated;
	}

	public TestItemBean getTestCaseInfo(int id) throws ClassNotFoundException, SQLException {
		TestItemBean testItemBean = new TestItemBean();
		
		String query = "SELECT ID, USER_STORY_ID, NAME, DESCRIPTION, EXPECTED_RESULTS"
				+ " FROM TEST_CASES WHERE ID = " + id ;
		
		ResultSet rset = dataAccess.getSelectResult(query);
		
		while (rset.next()) {
			testItemBean.setId(rset.getInt("ID"));
			testItemBean.setUserStoryId(rset.getInt("USER_STORY_ID"));
			testItemBean.setName(rset.getString("NAME"));
			testItemBean.setDescription(rset.getString("DESCRIPTION"));
			testItemBean.setExpectedResults(rset.getString("EXPECTED_RESULTS"));
		}
		dataAccess.closeConnection();

		return testItemBean;
	}

	public int update(TestItemBean testItemBean) throws ClassNotFoundException, SQLException {
		String query = "UPDATE TEST_CASES SET NAME=?, DESCRIPTION=?, EXPECTED_RESULTS=? WHERE ID=?";

		Connection conn = dataAccess.getConnection();
		PreparedStatement preparedStatement = conn.prepareStatement(query);

		preparedStatement.setString(1, testItemBean.getName());
		preparedStatement.setString(2, testItemBean.getDescription());
		preparedStatement.setString(3, testItemBean.getExpectedResults());
		preparedStatement.setInt(4, testItemBean.getId());
	
		int rowsUpdated = dataAccess.executeUpdate(preparedStatement);
		dataAccess.closeConnection();
			
		return rowsUpdated;
	}
	
	public int delete(int id) throws ClassNotFoundException, SQLException {
		String query = "DELETE FROM TEST_CASES WHERE ID = ?";
		int rowsUpdated = 0;

		Connection conn = dataAccess.getConnection();
		PreparedStatement preparedStatement = conn.prepareStatement(query);
		preparedStatement.setInt(1, id);
		
		rowsUpdated = dataAccess.executeUpdate(preparedStatement);
		dataAccess.closeConnection();

		return rowsUpdated;
	}

}
